import pandas as pd
import numpy as np
import datetime as datetime
import warnings
import openpyxl
from openpyxl import load_workbook

import xlrd
import xlwt

def concatAll():
    本月台账表路径 = '../文件/许昌/许昌裕同202405五险一金总台账.xls'
    五险一金数据中间表 = '../文件/许昌/test.xlsx'
    pd.set_option('display.max_columns', None)
    warnings.filterwarnings('ignore')
    # 作为所有结果的表头
    resultHead = ['姓名', '证件号码', '个人养老', '单位养老', '个人失业', '单位失业', '个人医疗', '单位医疗',
                  '大病个人',
                  '大病单位', '单位工伤', '单位生育', '个人公积金', '单位公积金', '公积金汇总']
    ##############################################
    # 读取第一个sheet页('许昌五险')的数据，并将需要的字段整理
    许昌五险sheet = pd.read_excel(本月台账表路径, sheet_name='许昌五险', header=[0, 1])

    column1 = []
    for val1, val2 in 许昌五险sheet.columns.tolist():  # 去除一些不必要的字段
        if ('许昌裕同印刷' in val1):
            str1 = val2
        else:
            str1 = val1 + '_' + val2
        column1.append(str1)
    许昌五险sheet.columns = column1
    许昌五险sheet['生育_单位部分'] = 0
    # print(许昌五险sheet.columns.tolist())
    head1 = ['姓名', '公民身份号码', '养老_个人部分', '养老_公司部分', '失业_个人部分', '失业_公司部分',
             '医疗_个人部分', '医疗_公司部分', '大病_个人部分', '大病_公司部分', '工伤（个人不承担费用）_公司部分',
             '生育_单位部分']
    许昌五险readDate = 许昌五险sheet[head1]
    print(许昌五险readDate['公民身份号码'].values)
    许昌五险readDate = 许昌五险readDate[许昌五险readDate['公民身份号码'].notnull()]
    许昌五险readDate.rename(columns={'公民身份号码': '证件号码'}, inplace=True)
    print(许昌五险readDate.columns.tolist())

    ##############################################
    # 读取第二个sheet页('许昌公积金')的数据，并将需要的字段整理
    许昌公积金sheet = pd.read_excel(本月台账表路径, sheet_name='许昌公积金')
    head2 = ['姓名', '证件号码', '个人部分(元)', '单位部分(元)', '汇缴总额(元)']
    许昌公积金readDate = 许昌公积金sheet[head2]
    许昌公积金readDate.rename(
        columns={'姓名': 'name', '个人部分(元)': '公积金个人部分', '单位部分(元)': '公积金单位部分',
                 '汇缴总额(元)': '公积金汇总金额'}, inplace=True)
    # print(许昌公积金readDate)
    # 拼接五险和公积金的表格数据
    mergeDate = pd.merge(许昌五险readDate, 许昌公积金readDate, how='outer', on=['证件号码'])
    mergeDate['姓名'].fillna(mergeDate['name'], inplace=True)  # 存在两条数据身份证号相同，但姓名不同的，所以拼接后需要覆盖一下姓名
    mergeDate.drop("name", axis=1, inplace=True)
    # 重新设置表头
    headmap2 = {}
    for item1, item2 in zip(resultHead, mergeDate):
        headmap2[item2] = item1
    # print(headmap2)
    mergeDate.rename(columns=headmap2, inplace=True)
    # mergeDate['缴交地方']='许昌'
    mergeDate.fillna(0, inplace=True)
    print(mergeDate.columns.tolist())

    ##############################################
    # 读取第三个sheet页('武汉')的数据，并将需要的字段整理
    武汉sheet = pd.read_excel(本月台账表路径, sheet_name='武汉')
    # print(武汉sheet.columns.tolist())
    武汉sheet['大病个人'] = 0
    武汉sheet['大病单位'] = 0
    武汉sheet['公积金汇总'] = 武汉sheet['个人公积金'] + 武汉sheet['单位公积金']
    head3 = ['姓名', '身份证号码', '个人养老', '单位养老', '个人失业', '单位失业', '个人医疗', '单位医疗', '大病个人',
             '大病单位', '单位工伤', '单位生育', '个人公积金', '单位公积金', '公积金汇总']
    武汉readDate = 武汉sheet[head3]
    # 重新设置表头
    headmap3 = {}
    for item1, item2 in zip(resultHead, 武汉readDate):
        headmap3[item2] = item1
    武汉readDate.rename(columns=headmap3, inplace=True)
    # 武汉readDate['缴交地方'] = '武汉'
    print(武汉readDate.columns.tolist())

    ##############################################
    # 读取第四个sheet页('深圳')的数据，并将需要的字段整理
    深圳sheet = pd.read_excel(本月台账表路径, sheet_name='深圳', header=[0, 1])
    column4 = []
    # for item in 深圳sheet.columns.tolist():
    for val1, val2 in 深圳sheet.columns.tolist():
        if ('Unnamed' in val1):
            str4 = val2
        else:
            str4 = val1 + '_' + val2
        column4.append(str4)
    深圳sheet.columns = column4
    深圳sheet['大病_个人交'] = 0
    深圳sheet['大病_单位交'] = 0
    深圳sheet['公积金_汇总'] = 深圳sheet['公积金_个人缴存'] + 深圳sheet['公积金_单位缴存']
    head4 = ['姓名', '身份证号', '养老保险_个人交', '养老保险_单位交', '失业保险_个人交', '失业保险_单位交',
             '医疗保险_个人交', '医疗保险_单位交', '大病_个人交', '大病_单位交', '工伤保险_单位交',
             '生育医疗_单位交', '公积金_个人缴存', '公积金_单位缴存', '公积金_汇总']
    深圳readDate = 深圳sheet[head4]

    # 重新设置表头
    headmap4 = {}
    for item1, item2 in zip(resultHead, 深圳readDate):
        headmap4[item2] = item1
    深圳readDate.rename(columns=headmap4, inplace=True)
    # 深圳readDate['缴交地方'] = '深圳'
    print(深圳readDate.columns.tolist())
    print(深圳readDate)
    ##############################################
    # 读取第五个sheet页('上海')的数据，并将需要的字段整理
    上海sheet = pd.read_excel(本月台账表路径, sheet_name='上海')
    # 把一些特殊字符的给去掉
    column5 = []
    for item in 上海sheet.columns.tolist():
        column5.append(item.replace('\n', '').split('（')[0])
    上海sheet.columns = column5
    上海sheet['个人大病'] = 0
    上海sheet['公司大病'] = 上海sheet['公司附加医疗']
    上海sheet['公司生育'] = 0
    # 上海sheet['公积金合计'] = 上海sheet['个人公积金'] +上海sheet['公司公积金']
    head5 = ['姓名', '证件号码', '个人养老', '公司养老', '个人失业', '公司失业', '个人医疗', '公司基本医疗', '个人大病',
             '公司大病', '公司工伤', '公司生育', '个人公积金', '公司公积金', '公积金合计']
    上海readDate = 上海sheet[head5]
    # 重新设置表头
    headmap5 = {}
    for item1, item2 in zip(resultHead, 上海readDate):
        headmap5[item2] = item1
    上海readDate.rename(columns=headmap5, inplace=True)
    # 上海readDate['缴交地方'] = '上海'
    print(上海readDate.columns.tolist())

    # 拼接所有的sheet页的数据
    resultDate = pd.concat([mergeDate, 武汉readDate, 深圳readDate, 上海readDate], ignore_index=True)
    resultDate = resultDate.astype(dtype={'姓名': str, '证件号码': str})
    print(resultDate)
    headFirst = ['姓名', '证件号码']  # 设置一下数据不用变 的表头用来区分‘first’和‘max’
    for head in resultDate.columns.tolist():
        if head in headFirst:
            resultDate[head] = resultDate.groupby('证件号码')[head].transform('first')
        else:
            resultDate[head] = resultDate.groupby('证件号码')[head].transform('sum')
    resultDate.drop_duplicates(subset='证件号码', inplace=True)

    # 求和('社保个人汇总','社保单位汇总')
    resultDate['社保个人汇总'] = resultDate['个人养老'] + resultDate['个人失业'] + resultDate['个人医疗'] + resultDate[
        '大病个人']
    resultDate['社保单位汇总'] = resultDate['单位养老'] + resultDate['单位失业'] + resultDate['单位医疗'] + resultDate[
        '大病单位'] + resultDate['单位工伤'] + resultDate['单位生育']
    resultDate.to_excel(五险一金数据中间表)


# concatAll()

def dataDoing():
    公积金数据接口数据 = '../文件/许昌/下载公积金数据接口数据2024-05-29.xls'
    五险一金数据中间表 = '../文件/许昌/test.xlsx'
    pd.set_option('display.max_columns', None)
    warnings.filterwarnings('ignore')
    # 以 {保险种类:{数据接口模板表头字段 : 在缴数据对应表头字段}} 做成类似的对应字典
    headMap = {'公积金金额合计（元）': '公积金汇总',
               '公积金单位缴费': '单位公积金',
               '公积金个人缴费': '个人公积金'}
    # 社保险种列表 = ['失业保险(单位缴纳)', '失业保险(个人缴纳)', '职工基本养老保险(单位缴纳)',
    #                 '职工基本养老保险(个人缴纳)', '工伤保险']

    itemDateframe = pd.read_excel(五险一金数据中间表, dtype={'证件号码': str})
    # print(itemDateframe)

    ###########################################################
    # 将读取的姓名数据转成列表,

    readExcelList = itemDateframe['证件号码'].values.tolist()
    print(f'本次读取的身份证号有:{readExcelList}')
    # 读取社保数据接口表中的数据
    readInterfaceExcel = pd.read_excel(公积金数据接口数据, dtype={'证件号码': str, '人员编码': str, '员工号': str})

    readInterfaceExcel['证件号码'] = readInterfaceExcel['证件号码'].astype(str)
    heardList = readInterfaceExcel.columns.tolist()
    print(f'当前数据接口模板表的表头为{heardList}')
    # 匹配出社保数据接口中证件号码包含在readExcelList中的数据
    readInterfaceDate = readInterfaceExcel[readInterfaceExcel['证件号码'].isin(readExcelList)]
    print(f'匹配身份证后的数据为{readInterfaceDate}')

    # 合并两个数据列表,并通过表头字段head ,获取其中需要的几列
    mergeDate = pd.merge(readInterfaceDate, itemDateframe, how='inner', on=['证件号码'])
    print("*****************")
    # 社保医保个人、单位缴交费用计算
    for i in range(0, mergeDate.shape[0]):
        for headKey, headValue in headMap.items():
            mergeDate.loc[i, headKey] = mergeDate.loc[i, headValue]  # 覆盖headMap中对应的列
    # 获取其中需要的表头数据
    resultDate = mergeDate[heardList]
    print(resultDate)

    # # 将获取所需要的表头后的数据转化为列表
    resultDateList = resultDate.values.tolist()
    print(f'将匹配后的数据转换成列表{resultDateList}')
    # # 获取其中的行数,并将所有行数加1
    indexList = readInterfaceDate.index.tolist()
    for i in range(0, len(indexList)):
        indexList[i] = indexList[i] + 1
    print(f'处理后的行数列表{indexList}')
    # 打开xls文件，并在指定行中写入文件
    workbook = xlrd.open_workbook(公积金数据接口数据)  # xlrd打开excel
    sheet = workbook.sheet_by_index(0)
    wb = xlwt.Workbook(encoding='utf-8')  # lxwt新建一个sheet页
    new_sheet = wb.add_sheet(sheet.name)
    for row_index in range(sheet.nrows):  # 循环每一行
        row = sheet.row_values(row_index)  # 旧数据中每一行的值
        if row_index in indexList:  # 如果行号与之前存储的匹配数据行列表中的值相同
            index = indexList.index(row_index)
            # 写入新数据到新的sheet
            for col_index, value in enumerate(resultDateList[index]):
                if str(value) == 'nan':
                    value = ''
                new_sheet.write(row_index, col_index, str(value))
            print(f'在{row_index}行中写入新数据:{resultDateList[index]}')
        else:
            # 写入旧数据到新的sheet
            for col_index, value in enumerate(row):
                new_sheet.write(row_index, col_index, value)
    wb.save(公积金数据接口数据)


# dataDoing()


def addOther():
    # 将其他的特殊人员写入到表格中
    公积金数据接口数据 = '../文件/许昌/下载公积金数据接口数据2024-05-29.xls'
    特殊在保人员文件路径 = '../文件/许昌/社保特殊在保人员.xls'
    warnings.filterwarnings('ignore')
    readDate = pd.read_excel(公积金数据接口数据, dtype={'证件号码': str, '人员编码': str, '员工号': str})
    readOtherDate = pd.read_excel(特殊在保人员文件路径, sheet_name='公积金特殊人员',
                                  dtype={'证件号码': str, '人员编码': str, '员工号': str})
    lenth = len(readOtherDate)

    if (lenth > 0):
        concatDate = pd.concat([readDate, readOtherDate], axis=0)
        print('特殊在保人员文件中数据不为空,拼接特殊人员数据')
    else:
        concatDate = readDate
        # Log.Info('特殊在保人员文件中数据为空,没有特殊人员往下执行')
        print('特殊在保人员文件中数据为空,没有特殊人员往下执行')
    # 拼接特殊在保人员的数据
    concatDateHead = concatDate.columns.tolist()
    pd.set_option('display.max_columns', None)
    # 设置groupby后的计算规则

    # 将证件号码，这一列的类型装换为str 类型，才可以用groupby进行分组求和
    # concatDate['证件号码'] = concatDate['证件号码'].astype(str)
    # # 根据证件号码进行分组求和 as_index
    headFirst = ['员工号', '人员姓名', '证件号码', '人员编码']  # 设置一下数据不用变 的表头用来区分‘first’和‘max’
    for head in concatDateHead:
        if head in headFirst:
            concatDate[head] = concatDate.groupby('证件号码')[head].transform('first')
        else:
            concatDate[head] = concatDate.groupby('证件号码')[head].transform('max')
    print(concatDate)
    # 如果需要，可以删除重复的'A'列值
    concatDate.drop_duplicates(subset='证件号码', inplace=True)
    itemDateframe = concatDate.reset_index(drop=True).fillna('')
    # 所有结果做保留两位有效数字处理
    colList = itemDateframe.columns.tolist()
    nameList = ['社保应收合计', '社保个人合计', '社保单位合计', '养老保险缴费基数', '养老保险个人交', '养老保险单位交',
                '医疗保险缴费基数', '医疗保险个人交', '医疗保险单位交', '工伤保险缴费基数', '工伤保险单位交',
                '失业保险缴费基数', '失业保险个人交', '失业保险单位交', '生育医疗缴费基数', '生育医疗单位交',
                '大病医疗单位', '大病医疗个人', '社保代扣', '公积金缴存基数（元）', '公积金单位缴存比例',
                '公积金个人缴存比例', '公积金单位缴费', '公积金个人缴费', '公积金金额合计（元）', '公积金代扣']

    for name in colList:
        if name in nameList:
            itemDateframe[name] = itemDateframe[name].map(lambda x: round(x, 2))
    Log.Info(self, itemDateframe)
    # itemDateframe.fillna('')
    print(itemDateframe)
    # 写入.xls文件
    wb = xlwt.Workbook(encoding='utf-8')  # lxwt新建一个sheet页
    #    new_sheet = wb.add_sheet(sheet.name)
    sheet = wb.add_sheet('Sheet0')
    # 将DataFrame的列名写入Excel表头
    for i, col_name in enumerate(itemDateframe.columns):
        sheet.write(0, i, col_name)
    # 将DataFrame的数据写入Excel
    for i, row in itemDateframe.iterrows():
        for j, col in enumerate(row):
            sheet.write(i + 1, j, col)
    wb.save(公积金数据接口数据)

# addOther()


def chuli():
    用户配置表 = '../文件/社保用户配置表.xlsx'
    list = ['120223197912152512', '41148119860611092X', '42058219801208275X', '420582198406072765',
            '420822198410015755', '431003198607201316', '452324198109140357']

    # 打开Excel文件
    workbook = openpyxl.load_workbook(用户配置表)
    # 选择要写入数据的表格
    sheet = workbook.active
    # 指定要写入数据的行和列
    row = 4
    column = 1
    # 写入数据
    sheet.cell(row=row, column=column, value=list)
    # 保存Excel文件
    workbook.save(用户配置表)

# chuli()


# 核对数据接口数据是否正确
def checkDate():

    特殊在保人员文件路径 = '../文件/许昌/社保特殊在保人员.xls'
    五险一金数据中间表 = '../文件/许昌/test.xlsx'
    下载公积金数据接口核对数据 = '../文件/许昌/下载公积金数据接口数据2024-05-29.xls'
    公积金核对结果文件路径='../文件/许昌/公积金核对结果.xlsx'
    pd.set_option('display.max_columns', None)

    warnings.filterwarnings('ignore')
    str_datemap = {'人员编码': str, '证件号码': str,'员工号':str}  # 设置一些读取excel时读取为str类型的数据

    readInterfaceExcel = pd.read_excel(下载公积金数据接口核对数据, usecols='A:G', dtype=str_datemap)
    print(f'本次下载的数据接口核对数据有{len(readInterfaceExcel)}条')
    readOtherExcel = pd.read_excel(特殊在保人员文件路径, usecols='A:G', dtype=str_datemap, sheet_name='公积金特殊人员')
    print(f'本次属于特殊人员的数据有{len(readOtherExcel)}条')

    lenth = len(readOtherExcel)
    if (lenth > 0):
        readInterfaceExcel = pd.concat([readInterfaceExcel, readOtherExcel], axis=0)
        print('特殊在保人员文件中数据不为空,拼接特殊人员数据')
    # 拼接特殊人员表后,如果两行全部数据都想相同的,就去除一行
    readDate = readInterfaceExcel.drop_duplicates(keep='first')
    print(readDate)
    resultDateList = []
    number = 0
    mergeAllDate = None
    str_datemap1 = {'姓名': str, '证件号码': str}

    itemDateframe = pd.read_excel(五险一金数据中间表, dtype=str_datemap1)
    itemDateframe = itemDateframe.fillna(0)
    heard = ['姓名', '证件号码', '公积金汇总', '单位公积金','个人公积金']
    mergeAllDate = itemDateframe[heard]
    print(f'本次属于在缴人员的数据有{len(mergeAllDate)}条')
    # print(caoncatDate)
    mergeDate = pd.merge(readDate, mergeAllDate, how='outer', on=['证件号码'])
    columnsList = mergeDate.columns.tolist()
    # 可以根据需要添加更多列对(需要相互比较的列明)
    column_pairs = [('公积金金额合计（元）', '公积金汇总'),
                    ('公积金单位缴费', '单位公积金'),
                    ('公积金个人缴费', '个人公积金')]
    # ,('生育医疗单位交', '生育保险应缴费额')
    # 使用列表解析来创建条件 判断是否相等,不相等的为ture ,相等的为false
    # conditions = []
    # for col1, col2 in column_pairs:
    #     if col1 in columnsList:
    #         result = mergeDate[col1] != mergeDate[col2]
    #         conditions.append(result)
    conditions = [mergeDate[col1] != mergeDate[col2] for col1, col2 in column_pairs]
    print(conditions)
    # # 使用any函数来检查任何条件是否为True、
    mask = pd.concat(conditions, axis=1).any(axis=1)
    #
    # # 使用布尔mask来筛选行
    filtered_df = mergeDate[mask]
    print(f'得到下载数据与缴交数据不相同的数据有{len(filtered_df)}条')
    filtered_df.to_excel(公积金核对结果文件路径)
checkDate()
